package biz.bbtec.sbm.client.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import biz.bbtec.sbm.client.bean.BusinessBean;
import biz.bbtec.sbm.client.bean.ClientBean;
import biz.bbtec.sbm.db.dbConnection;
import biz.bbtec.sbm.user.bean.UserBean;

import com.opensymphony.xwork2.ActionContext;

public class BusinessService {

	dbConnection db = new dbConnection();

	public List showallbusiness(ClientBean client,int pagenow, int pagesize) throws Exception {
		List<BusinessBean> list = new ArrayList<BusinessBean>();
		Map session = ActionContext.getContext().getSession();
		if (client == null) {
			int clientid = (Integer) session.get("clientid");
			client = new ClientBean();
			client.setClientid(clientid);
			session.remove("clientid");
		} 
		int now=pagenow*pagesize;
		String sql = "select * from business  WHERE clientid = "
				+ client.getClientid() + " limit "+now+","+pagesize+"";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		try {
			past = conn.prepareStatement(sql);
			//past.setInt(1, now);
			//past.setInt(2, pagesize);
			rs = past.executeQuery();
			while (rs.next()) {
				BusinessBean business = new BusinessBean();
				business.setBizid(rs.getInt("bizid"));
				System.out.println(rs.getInt("bizid"));
				String descrp=rs.getString("description");
				descrp=descrp.replace(" ", "&nbsp;");
				descrp=descrp.replace("\n", "<br/>");
				business.setDescription(descrp);
				business.setName(rs.getString("name"));
				business.setUserid(rs.getInt("userid"));
				business.setManager(rs.getString("manager"));
				business.setPhone(rs.getString("phone"));
				business.setClientid(client.getClientid());
				business.setStatus(rs.getInt("status"));
				int bizid = business.getBizid();
				String sql2 = "select count(*) from note where bizid =" + "'"
						+ bizid + "'" + "group by bizid";

				System.out.print(sql2);
				try {
					Statement stmt = conn.createStatement();
					ResultSet rs2 = stmt.executeQuery(sql2);
					if (rs2.next()) {
						int noteno = rs2.getInt(1);
						business.setNoteno(noteno);
					}

				} catch (SQLException e) {
					e.printStackTrace();
				}
				list.add(business);
			}
			conn.close();
			past.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}
	public int CountAllBusiness(ClientBean client){
		int total=0;
		Map session=ActionContext.getContext().getSession();
		//UserBean user=(UserBean)session.get("user");
		//int userid= user.getUserid();
		if (client == null) {
			int clientid = (Integer) session.get("clientid");
			client = new ClientBean();
			client.setClientid(clientid);
		} 
		//int clientid=(Integer) session.get("clientid");
		String sql1="select count(*)from business WHERE	  clientid="+client.getClientid()+"";
		Connection conn=db.getConnection();
		ResultSet rs=null;
		PreparedStatement past=null;
		try{
			past=conn.prepareStatement(sql1);
			rs=past.executeQuery();
			if(rs.next()){
				total=rs.getInt(1);
			}
			rs.close();
			past.close();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			db.closeConnection(conn);
		}
		return total;
	}
	public List showUsers(UserBean userbean) {
		List<UserBean> list = new ArrayList<UserBean>();

		String sql1 = "select userid,name from user where orgid = ?";

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;

		try {
			past = conn.prepareStatement(sql1);
			past.setInt(1, userbean.getOrgid());
			rs = past.executeQuery();
			while (rs.next()) {
				UserBean user = new UserBean();
				user.setUserid(rs.getInt(1));
				user.setName(rs.getString(2));
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}

		return list;
	}

	public boolean addbusiness(BusinessBean business) {
		boolean flag = false;
		Map session = ActionContext.getContext().getSession();

		UserBean user = (UserBean) session.get("user");
		int clientid = (Integer) session.get("clientid");
		String sql = "insert into business(name, description, clientid, userid,manager,phone,status) values ("
				+ "'"
				+ business.getName()
				+ "'"
				+ ","
				+ "'"
				+ business.getDescription()
				+ "'"
				+ ","
				+ "'"
				+ clientid
				+ "'"
				+ ","
				+ "'"
				+ user.getUserid()
				+ "'"
				+ ","
				+ "'"
				+ business.getManager()
				+ "'"
				+ ","
				+ "'"
				+ business.getPhone()
				+ "'" + "," + "'" + business.getStatus() + "'" + ")";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.executeUpdate();
			past.close();
			conn.close();
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public boolean deletebusiness(BusinessBean business) {
		boolean flag = false;
		String sql = "delete from business where bizid = " + "'"
				+ business.getBizid() + "'";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.executeUpdate(sql);
			past.close();
			conn.close();
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public boolean modifybusiness(BusinessBean business) {
		boolean flag = false;
		String sql = "update business set name=?, description=?,manager=?,phone=?,status=? where bizid=?";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {

			past = conn.prepareStatement(sql);
			past.setString(1, business.getName());
			past.setString(2, business.getDescription());
			past.setString(3, business.getManager());
			past.setString(4, business.getPhone());
			past.setInt(5, business.getStatus());
			past.setInt(6, business.getBizid());
			past.executeUpdate();
			past.close();
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public BusinessBean showonebusiness(BusinessBean businessbean) {
		BusinessBean business = new BusinessBean();
		String sql = "select * from business where bizid = ? ";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.setInt(1, businessbean.getBizid());
			ResultSet rs = past.executeQuery();
			if (!rs.next()) {
				return null;
			}
			business.setBizid(rs.getInt("bizid"));
			business.setDescription(rs.getString("description"));
			business.setName(rs.getString("name"));
			business.setManager(rs.getString("manager"));
			business.setPhone(rs.getString("phone"));
			business.setStatus(rs.getInt("status"));
			rs.close();
			past.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return business;
	}

}
